package com.insight.datasource.adapter;

import com.insight.common.util.EncryptionUtil;
import com.insight.entity.DataSource;
import com.insight.entity.MetadataColumn;
import com.insight.entity.MetadataIndex;
import com.insight.entity.MetadataTable;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.*;

/**
 * T056: Oracle元数据提取器实现
 * 使用Oracle数据字典视图提取元数据
 *
 * <p>支持的Oracle版本: 12c+, 18c+, 19c+, 21c+, 23c
 *
 * <p>核心查询视图:
 * <ul>
 *   <li>USER_TABLES / ALL_TABLES - 表元数据</li>
 *   <li>USER_TAB_COLUMNS / ALL_TAB_COLUMNS - 列元数据</li>
 *   <li>USER_INDEXES / ALL_INDEXES - 索引元数据</li>
 *   <li>USER_IND_COLUMNS / ALL_IND_COLUMNS - 索引列元数据</li>
 *   <li>USER_TAB_COMMENTS / ALL_TAB_COMMENTS - 表注释</li>
 *   <li>USER_COL_COMMENTS / ALL_COL_COMMENTS - 列注释</li>
 * </ul>
 *
 * @author Insight Team
 */
@Slf4j
public class OracleMetadataExtractor implements MetadataExtractor {

    /**
     * 连接超时时间(秒)
     */
    private static final int CONNECTION_TIMEOUT = 10;

    /**
     * 查询超时时间(秒)
     */
    private static final int QUERY_TIMEOUT = 30;

    @Override
    public List<MetadataTable> extractTables(DataSource dataSource, String schemaFilter) {
        log.info("开始提取Oracle表列表: dataSource={}, schema={}", dataSource.getName(), schemaFilter);

        List<MetadataTable> tables = new ArrayList<>();

        // Oracle使用用户名作为schema
        String schema = schemaFilter != null ? schemaFilter.toUpperCase() : dataSource.getUsername().toUpperCase();

        String sql = """
                SELECT
                    t.OWNER AS table_schema,
                    t.TABLE_NAME AS table_name,
                    t.TABLESPACE_NAME,
                    t.NUM_ROWS AS row_count,
                    c.COMMENTS AS table_comment,
                    s.BYTES AS total_size
                FROM ALL_TABLES t
                LEFT JOIN ALL_TAB_COMMENTS c
                    ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAME
                LEFT JOIN (
                    SELECT OWNER, SEGMENT_NAME, SUM(BYTES) AS BYTES
                    FROM DBA_SEGMENTS
                    WHERE SEGMENT_TYPE = 'TABLE'
                    GROUP BY OWNER, SEGMENT_NAME
                ) s ON t.OWNER = s.OWNER AND t.TABLE_NAME = s.SEGMENT_NAME
                WHERE t.OWNER = ?
                  AND t.TEMPORARY = 'N'
                ORDER BY t.TABLE_NAME
                """;

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);
            pstmt.setString(1, schema);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    MetadataTable table = new MetadataTable();
                    table.setDataSource(dataSource);
                    table.setTableSchema(rs.getString("table_schema"));
                    table.setTableName(rs.getString("table_name"));
                    table.setTableType("TABLE");
                    table.setTableComment(rs.getString("table_comment"));

                    // Oracle的NUM_ROWS需要通过ANALYZE更新
                    Long rowCount = rs.getLong("row_count");
                    if (!rs.wasNull()) {
                        table.setRowCount(rowCount);
                    }

                    // 总大小(字节)
                    Long totalSize = rs.getLong("total_size");
                    if (!rs.wasNull()) {
                        // table.setDataLength removed - field does not exist in schema
                    }

                    tables.add(table);
                }
            }

            log.info("成功提取Oracle表列表: dataSource={}, tableCount={}", dataSource.getName(), tables.size());
            return tables;

        } catch (SQLException e) {
            log.error("提取Oracle表列表失败: dataSource={}, error={}", dataSource.getName(), e.getMessage(), e);
            throw new RuntimeException("提取Oracle表列表失败: " + e.getMessage(), e);
        }
    }

    @Override
    public List<MetadataColumn> extractColumns(DataSource dataSource, MetadataTable table) {
        log.info("开始提取Oracle列信息: table={}", table.getFullName());

        List<MetadataColumn> columns = new ArrayList<>();

        String sql = """
                SELECT
                    c.COLUMN_NAME,
                    c.COLUMN_ID AS ordinal_position,
                    c.DATA_DEFAULT AS column_default,
                    c.NULLABLE,
                    c.DATA_TYPE,
                    c.DATA_LENGTH,
                    c.DATA_PRECISION,
                    c.DATA_SCALE,
                    cm.COMMENTS AS column_comment,
                    CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'Y' ELSE 'N' END AS is_primary_key,
                    c.IDENTITY_COLUMN
                FROM ALL_TAB_COLUMNS c
                LEFT JOIN ALL_COL_COMMENTS cm
                    ON c.OWNER = cm.OWNER
                    AND c.TABLE_NAME = cm.TABLE_NAME
                    AND c.COLUMN_NAME = cm.COLUMN_NAME
                LEFT JOIN (
                    SELECT acc.OWNER, acc.TABLE_NAME, acc.COLUMN_NAME
                    FROM ALL_CONSTRAINTS ac
                    JOIN ALL_CONS_COLUMNS acc
                        ON ac.OWNER = acc.OWNER
                        AND ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME
                    WHERE ac.CONSTRAINT_TYPE = 'P'
                        AND ac.OWNER = ?
                        AND ac.TABLE_NAME = ?
                ) pk ON c.OWNER = pk.OWNER
                    AND c.TABLE_NAME = pk.TABLE_NAME
                    AND c.COLUMN_NAME = pk.COLUMN_NAME
                WHERE c.OWNER = ?
                  AND c.TABLE_NAME = ?
                ORDER BY c.COLUMN_ID
                """;

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);
            String schemaUpper = table.getTableSchema().toUpperCase();
            String tableNameUpper = table.getTableName().toUpperCase();

            pstmt.setString(1, schemaUpper);
            pstmt.setString(2, tableNameUpper);
            pstmt.setString(3, schemaUpper);
            pstmt.setString(4, tableNameUpper);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    MetadataColumn column = new MetadataColumn();
                    column.setTable(table);
                    column.setColumnName(rs.getString("COLUMN_NAME"));
                    column.setColumnPosition(rs.getInt("ordinal_position"));

                    // Oracle的DATA_DEFAULT可能包含空格和换行
                    String defaultValue = rs.getString("column_default");
                    if (defaultValue != null) {
                        defaultValue = defaultValue.trim();
                    }
                    column.setDefaultValue(defaultValue);

                    column.setNullable("Y".equals(rs.getString("NULLABLE")));
                    column.setDataType(rs.getString("DATA_TYPE"));

                    // 字符类型长度
                    Integer dataLength = rs.getInt("DATA_LENGTH");
                    if (!rs.wasNull() && isCharacterType(rs.getString("DATA_TYPE"))) {
                        column.setColumnLength(dataLength);
                    }

                    // 数值类型精度和小数位
                    Integer dataPrecision = rs.getInt("DATA_PRECISION");
                    if (!rs.wasNull()) {
                        column.setColumnPrecision(dataPrecision);
                    }

                    Integer dataScale = rs.getInt("DATA_SCALE");
                    if (!rs.wasNull()) {
                        column.setColumnScale(dataScale);
                    }

                    // 主键判断
                    column.setIsPrimaryKey("Y".equals(rs.getString("is_primary_key")));

                    // Oracle 12c+的IDENTITY列(自增)判断
                    String identityColumn = rs.getString("IDENTITY_COLUMN");

                    column.setColumnComment(rs.getString("column_comment"));

                    columns.add(column);
                }
            }

            log.info("成功提取Oracle列信息: table={}, columnCount={}", table.getFullName(), columns.size());
            return columns;

        } catch (SQLException e) {
            log.error("提取Oracle列信息失败: table={}, error={}", table.getFullName(), e.getMessage(), e);
            throw new RuntimeException("提取Oracle列信息失败: " + e.getMessage(), e);
        }
    }

    @Override
    public List<MetadataIndex> extractIndexes(DataSource dataSource, MetadataTable table) {
        log.info("开始提取Oracle索引信息: table={}", table.getFullName());

        Map<String, MetadataIndex> indexMap = new LinkedHashMap<>();

        String sql = """
                SELECT
                    i.INDEX_NAME,
                    i.INDEX_TYPE,
                    i.UNIQUENESS,
                    ic.COLUMN_NAME,
                    ic.COLUMN_POSITION
                FROM ALL_INDEXES i
                JOIN ALL_IND_COLUMNS ic
                    ON i.OWNER = ic.INDEX_OWNER
                    AND i.INDEX_NAME = ic.INDEX_NAME
                WHERE i.TABLE_OWNER = ?
                  AND i.TABLE_NAME = ?
                ORDER BY i.INDEX_NAME, ic.COLUMN_POSITION
                """;

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);
            String schemaUpper = table.getTableSchema().toUpperCase();
            String tableNameUpper = table.getTableName().toUpperCase();

            pstmt.setString(1, schemaUpper);
            pstmt.setString(2, tableNameUpper);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    String indexName = rs.getString("INDEX_NAME");
                    String columnName = rs.getString("COLUMN_NAME");
                    String uniqueness = rs.getString("UNIQUENESS");
                    String indexType = rs.getString("INDEX_TYPE");

                    // 获取或创建索引对象
                    MetadataIndex index = indexMap.computeIfAbsent(indexName, k -> {
                        MetadataIndex newIndex = new MetadataIndex();
                        newIndex.setTable(table);
                        newIndex.setIndexName(indexName);
                        newIndex.setIsUnique("UNIQUE".equals(uniqueness));
                        newIndex.setIndexType(indexType.toLowerCase());
                        return newIndex;
                    });

                    // 追加列名(复合索引)
                    String existingColumns = index.getColumnNames();
                    if (existingColumns == null || existingColumns.isEmpty()) {
                        index.setColumnNames(columnName);
                    } else {
                        index.setColumnNames(existingColumns + "," + columnName);
                    }
                }
            }

            List<MetadataIndex> indexes = new ArrayList<>(indexMap.values());

            log.info("成功提取Oracle索引信息: table={}, indexCount={}", table.getFullName(), indexes.size());
            return indexes;

        } catch (SQLException e) {
            log.error("提取Oracle索引信息失败: table={}, error={}", table.getFullName(), e.getMessage(), e);
            throw new RuntimeException("提取Oracle索引信息失败: " + e.getMessage(), e);
        }
    }

    @Override
    public boolean testConnection(DataSource dataSource) {
        log.info("测试Oracle连接: dataSource={}", dataSource.getName());

        String sql = "SELECT 1 FROM DUAL";

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(CONNECTION_TIMEOUT);

            try (ResultSet rs = pstmt.executeQuery()) {
                boolean result = rs.next() && rs.getInt(1) == 1;
                log.info("Oracle连接测试{}: dataSource={}",
                        result ? "成功" : "失败", dataSource.getName());
                return result;
            }

        } catch (SQLException e) {
            log.error("Oracle连接测试失败: dataSource={}, error={}",
                    dataSource.getName(), e.getMessage());
            return false;
        }
    }

    @Override
    public String getDatabaseVersion(DataSource dataSource) {
        log.info("获取Oracle版本信息: dataSource={}", dataSource.getName());

        String sql = "SELECT BANNER FROM V$VERSION WHERE ROWNUM = 1";

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    String version = rs.getString(1);
                    log.info("Oracle版本: dataSource={}, version={}", dataSource.getName(), version);
                    return version;
                }
            }

        } catch (SQLException e) {
            log.error("获取Oracle版本失败: dataSource={}, error={}",
                    dataSource.getName(), e.getMessage());
        }

        return "未知版本";
    }

    @Override
    public String getCurrentSchema(DataSource dataSource) {
        log.info("获取Oracle当前schema: dataSource={}", dataSource.getName());

        String sql = "SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL";

        try (Connection conn = createConnection(dataSource);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setQueryTimeout(QUERY_TIMEOUT);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    String schema = rs.getString(1);
                    log.info("Oracle当前schema: dataSource={}, schema={}", dataSource.getName(), schema);
                    return schema;
                }
            }

        } catch (SQLException e) {
            log.error("获取Oracle当前schema失败: dataSource={}, error={}",
                    dataSource.getName(), e.getMessage());
        }

        return null;
    }

    /**
     * 创建Oracle数据库连接
     *
     * @param dataSource 数据源配置
     * @return JDBC连接
     * @throws SQLException 连接异常
     */
    private Connection createConnection(DataSource dataSource) throws SQLException {
        String jdbcUrl = buildJdbcUrl(dataSource);

        Properties props = new Properties();
        props.setProperty("user", dataSource.getUsername() != null ? dataSource.getUsername() : "");
        // 解密密码 - 使用passwordEncrypted字段
        String decryptedPassword = "";
        if (dataSource.getPasswordEncrypted() != null && !dataSource.getPasswordEncrypted().isEmpty()) {
            try {
                decryptedPassword = EncryptionUtil.decrypt(dataSource.getPasswordEncrypted());
            } catch (Exception e) {
                log.error("解密密码失败: dataSource={}, error={}", dataSource.getName(), e.getMessage());
                throw new SQLException("密码解密失败: " + e.getMessage(), e);
            }
        }
        props.setProperty("password", decryptedPassword);
        props.setProperty("oracle.net.CONNECT_TIMEOUT", String.valueOf(CONNECTION_TIMEOUT * 1000));

        log.debug("创建Oracle连接: url={}", jdbcUrl);

        DriverManager.setLoginTimeout(CONNECTION_TIMEOUT);
        return DriverManager.getConnection(jdbcUrl, props);
    }

    /**
     * 构建Oracle JDBC URL
     *
     * @param dataSource 数据源配置
     * @return JDBC URL
     */
    private String buildJdbcUrl(DataSource dataSource) {
        // Oracle使用SID或Service Name连接
        // 这里使用Service Name格式
        return String.format("jdbc:oracle:thin:@//%s:%d/%s",
                dataSource.getHost(),
                dataSource.getPort(),
                dataSource.getDatabaseName());
    }

    /**
     * 判断是否为字符类型
     *
     * @param dataType 数据类型
     * @return 是否为字符类型
     */
    private boolean isCharacterType(String dataType) {
        if (dataType == null) {
            return false;
        }
        return dataType.startsWith("VARCHAR") ||
                dataType.startsWith("CHAR") ||
                dataType.equals("NVARCHAR2") ||
                dataType.equals("NCHAR");
    }
}
